﻿<?xml version="1.0"?>
<!-- xsl:stylesheet
	xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
	xmlns:UML='href://org.omg/UML/1.1'
	xmlns:CWM="org.omg.CWM1.0/ObjectModel" xmlns:UML="org.omg.CWM1.0/Core" xmlns:CWMRDB="org.omg.CWM1.0/Relational"
	version="1.0" -->
<xsl:stylesheet 
	xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
	xmlns:CWM="org.omg.CWM1.0/ObjectModel" xmlns:UML="org.omg.CWM1.0/Core" xmlns:CWMRDB="org.omg.CWM1.0/Relational"
	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:rad="http://rad.nowitworks.eu/FN"	
	version="1.0">
	<xsl:output method="html"  version="4.0" encoding="utf-8" indent="yes"/>
	<xsl:key name="type-index" match="//CWMRDB:SQLSimpleType" use="@xmi.id"/>

	<!--
		This transform summarizes in HTML an XMI 1.1 generated by Business Objects DS 
		(NB! BO DS generats wrong namespaces) 
	-->
	<xsl:template match="/" >
-- DROP TABLE IF EXISTS meta$table;
CREATE TABLE IF NOT EXISTS meta$table ( 
    id          INTEGER NOT NULL
                PRIMARY KEY, -- alias for oid, rowid, _rowid_
    schema      TEXT    NOT NULL,
    name        TEXT    NOT NULL,
    description TEXT,
    UNIQUE ( schema, name ) 
);
-- DROP TABLE IF EXISTS meta$column;
CREATE TABLE IF NOT EXISTS meta$column ( 
    id          INTEGER NOT NULL
                        PRIMARY KEY, -- alias for oid, rowid, _rowid_
    table_id    INTEGER NOT NULL,
    name        TEXT    NOT NULL,
    type        TEXT    NOT NULL,
    is_nullable BOOLEAN,
    is_pk       BOOLEAN,
    length      INTEGER,
    precision   INTEGER,
    scale       INTEGER,
    description TEXT,
    not_empty_value_count	INTEGER NULL,
	schema		TEXT,	-- denormalization
	table_name	TEXT,	-- denormalization
    UNIQUE ( table_id, name ),
    FOREIGN KEY ( table_id ) REFERENCES meta$table( id )
);
DROP INDEX IF EXISTS meta$column_idx;
<xsl:variable name="apos">'</xsl:variable>
<xsl:variable name="quote">"</xsl:variable>
<xsl:variable name="schema" select="//CWMRDB:Schema/@namespace"/>
<xsl:for-each select="//CWMRDB:Table">INSERT OR REPLACE INTO meta$table ( schema, name, description )
VALUES ( '<xsl:value-of select="$schema"/>', '<xsl:value-of select="@name"/>', '<xsl:value-of select="UML:ModelElement.taggedValue/UML:TaggedValue[@tag='TechnicalDescription']/@value"/>');
</xsl:for-each>
DROP TABLE IF EXISTS tmp$column;
DROP TABLE IF EXISTS tmp$column;
CREATE TEMPORARY TABLE tmp$column (
	schema TEXT NOT NULL,
	table_name INTEGER NOT NULL,
	name TEXT NOT NULL,
	type TEXT NOT NULL,
	is_nullable BOOLEAN,
	is_pk BOOLEAN,
	length INTEGER,
	precision INTEGER,
	scale INTEGER,
	description TEXT );
<xsl:for-each select="//CWMRDB:Table">
	<xsl:variable name="table-name" select="@name"/>
	<xsl:variable name="pk" select="CWM:Namespace.ownedElement/CWMRDB:PrimaryKey"/>
INSERT INTO tmp$column (schema, table_name, name, type, is_nullable, is_pk, length, precision, scale, description)<xsl:for-each select="CWM:Classifier.feature/CWMRDB:Column">
		<xsl:variable name="id" select="@xmi.id" />
		<xsl:variable name="name" select="translate(@name,$apos,$quote)" />
		<xsl:variable name="mapped-type" select="key('type-index',@type)" />
		<xsl:variable name="type">
			<xsl:choose>
				<xsl:when test="$mapped-type"><xsl:value-of select="$mapped-type/@name"/></xsl:when>
				<xsl:when test="@type='di_char'">varchar</xsl:when>
				<xsl:when test="@type='di_decimal'">numeric</xsl:when>
				<xsl:when test="contains(@type,'di_')">
					<xsl:value-of select="substring(@type, 4)"/>
				</xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="@type"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="is-pk"><xsl:if test="$pk/CWM:UniqueKey.feature/CWM:StructuralFeature[@xmi.idref=$id] or $pk/@feature=$id">1</xsl:if></xsl:variable>
		<xsl:variable name="is-nullable"><xsl:if test="@isNullable='columnNullable'">1</xsl:if></xsl:variable>
		<xsl:variable name="length">
			<xsl:choose>
				<xsl:when test="@length"><xsl:value-of select="@length"/></xsl:when>
				<xsl:otherwise>NULL</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="precision">
			<xsl:choose>
				<xsl:when test="@precision"><xsl:value-of select="@precision"/></xsl:when>
				<xsl:otherwise>NULL</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="scale">
			<xsl:choose>
				<xsl:when test="@scale"><xsl:value-of select="@scale"/></xsl:when>
				<xsl:otherwise>NULL</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="desc" select="translate(UML:ModelElement.taggedValue/UML:TaggedValue[@tag='TechnicalDescription']/@value,$apos,$quote)" />
SELECT '<xsl:value-of select="$schema"/>', '<xsl:value-of select="$table-name"/>', '<xsl:value-of select="$name"/>', '<xsl:value-of select="$type"/>', '<xsl:value-of select="$is-nullable"/>', '<xsl:value-of select="$is-pk"/>', <xsl:value-of select="$length"/>, <xsl:value-of select="$precision"/>, <xsl:value-of select="$scale"/>, '<xsl:value-of select="$desc"/>'<xsl:if test="position()!=last()"> UNION ALL </xsl:if></xsl:for-each>;
</xsl:for-each>
INSERT OR REPLACE INTO meta$column
  ( table_id, name, type, is_nullable, is_pk, length, precision, scale, description, schema, table_name, not_empty_value_count)
SELECT
	t.id AS table_id,
	tc.name, tc.type, tc.is_nullable, tc.is_pk, tc.length, tc.precision, tc.scale, IFNULL( c.description, tc.description) AS description,
	tc.schema, tc.table_name, c.not_empty_value_count
FROM tmp$column tc JOIN meta$table t ON t.schema = tc.schema AND t.name = tc.table_name
  LEFT JOIN meta$column c ON t.schema = c.schema AND t.name = c.table_name AND c.name=tc.name;
CREATE INDEX IF NOT EXISTS meta$column_idx ON meta$column( table_id);
</xsl:template>


<!-- This transform summarizes in HTML an XMI 1.1 / UML 1.3 file
	(from Unicorn or Rose add-in e.g.) 
-->

<xsl:template match="UML:Class/*/UML:Attribute" >
	<xsl:variable name="class-id" select="../../@xmi.id" />
	<xsl:variable name="attribute-id" select="@xmi.id/text()" />
	<tr><!-- Class -->
		<td><xsl:value-of select="../../@name" /></td>
		<td><xsl:value-of select="
				// UML:TaggedValue [ @tag = 'documentation' ] 
				[ @modelElement = $class-id ]
				/ UML:TaggedValue.value
				" /></td>
		<!-- Attribute -->
		<td><xsl:value-of select="      @name" /></td>
		<td> <xsl:apply-templates mode="documentation" select="$attribute-id" /> </td>
		<td><xsl:value-of select="      key('type', @type)/@name" /></td>
		<td><xsl:value-of select="      @type" /></td>
		</tr><xsl:text>
	</xsl:text>
</xsl:template>

<xsl:key name = "type"
   match = "//UML:DataType"
   use = "@xmi.id" />

<xsl:template mode="documentation" match='text()' >
<xsl:value-of select="
				// UML:TaggedValue [ @tag = 'documentation' ] 
				[ @modelElement = . ]
				/ UML:TaggedValue.value
				" />
</xsl:template>

<!--
<xsl:template match="UML:Class" >
	<tr>
		<td><xsl:value-of select="@name" /></td>
	<xsl:apply-templates mode="fields" />
		</tr><xsl:text>
	</xsl:text>
</xsl:template>

<xsl:template match="UML:Attribute" mode="fields" >
		<td><xsl:value-of select="@name" /></td>
</xsl:template>
-->
 <xsl:template match="text()" />

</xsl:stylesheet>
